import time
import pymysql
import numpy as np

def database2dic(cursor):
    dic_fundV = {}
    dic_preferenceV = {}
    dic_scoreV = {}

    sql = "select * from api_fundvector"
    cursor.execute(sql)
    fundVectors = cursor.fetchall()
    for fundVector in fundVectors:
        dic_fundV[fundVector[0]] = np.array(fundVector[1:])

    sql = "select * from api_preferencevector"
    cursor.execute(sql)
    preferenceVectors = cursor.fetchall()
    for preferenceVector in preferenceVectors:
        dic_preferenceV[preferenceVector[0]] = np.array(preferenceVector[1:])

    sql = "select * from api_userfundscore"
    cursor.execute(sql)
    scores = cursor.fetchall()
    for score in scores:
        if score[1] not in dic_scoreV:
            dic_scoreV[score[1]] = {}
        dic_scoreV[score[1]][score[2]] = list(score[3:])

    print("load data ok!")
    return dic_fundV, dic_preferenceV, dic_scoreV


def updatePreferenceVector(dic_fundV, dic_preferenceV, dic_scoreV, lr=0.01):
    for user_id in dic_scoreV.keys():
        dic_user_id = dic_scoreV[user_id]
        for fund_id in dic_user_id.keys():
            score = dic_user_id[fund_id]
            if score[0] == 1:
                gradient = 2 * (np.dot(dic_preferenceV[user_id], dic_fundV[fund_id]) - score[1]) * dic_fundV[fund_id]
                dic_preferenceV[user_id] = dic_preferenceV[user_id] - lr * gradient
    # *** SET MINUS VALUES TO 0 AND execute inner-group normalization***
    for user_id in dic_preferenceV.keys():
        preferenceV = dic_preferenceV[user_id]
        preferenceV = np.maximum(preferenceV, 0)
        for i in range(9):
            preferenceV[4*i:4*i+4] = preferenceV[4*i:4*i+4] / (sum(preferenceV[4*i:4*i+4]) + 0.0001)
        preferenceV[36:41] = preferenceV[36:41] / (sum(preferenceV[36:41]) + 0.0001)
        preferenceV[41:45] = preferenceV[41:45] / (sum(preferenceV[41:45]) + 0.0001)
        dic_preferenceV[user_id] = preferenceV
    print("update preference vector ok")


def updateFundGrowth(cursor, dic_fundV):
    sql = "select * from api_fund"
    cursor.execute(sql)
    funds = cursor.fetchall()
    growths = [[], [], [], [], [], []]
    for fund in funds:
        for i in range(6):
            growths[i].append(fund[7+i])
    for growth in growths:
        growth.sort()
    bounds = [[], [], [], [], [], []]
    for i in range(6):
        length = len(growths[i])
        bounds[i].append(growths[i][length // 4])
        bounds[i].append(growths[i][length // 2])
        bounds[i].append(growths[i][3 * length // 4])
    print("growth bounds: ", end='')
    print(bounds)
    for fund in funds:
        vector_part = []
        for i in range(6):
            value = fund[7+i]
            if value < bounds[i][0]:
                vector_part = vector_part + [1, 0, 0, 0]
            elif bounds[i][0] <= value < bounds[i][1]:
                vector_part = vector_part + [0, 1, 0, 0]
            elif bounds[i][1] <= value < bounds[i][2]:
                vector_part = vector_part + [0, 0, 1, 0]
            elif bounds[i][2] <= value:
                vector_part = vector_part + [0, 0, 0, 1]
            else:
                vector_part = vector_part + [0, 0, 0, 0]
        fund_id = fund[0]
        dic_fundV[fund_id][:24] = vector_part[:]
    print('update fund growth ok')


def updateFundRateTypeScale(cursor, dic_fundV):
    sql = "select * from api_fund"
    cursor.execute(sql)
    funds = cursor.fetchall()
    rates = [[], [], []]
    sizes = []
    bounds_rate = [[], [], []]
    bounds_size = []
    for fund in funds:
        fund_id = fund[0]
        # collect rate
        sql = "select * from api_rate where fund_id='{}'".format(fund_id)
        cursor.execute(sql)
        try:
            rate = cursor.fetchall()[0]
            if rate[1] != '---':
                rates[0].append(float(rate[1][:-1]))
            if rate[2] != '---':
                rates[1].append(float(rate[2][:-1]))
            if rate[3] != '---':
                rates[2].append(float(rate[3][:-1]))
        except:
            pass
        # collect scale
        sql = "select * from api_size where fund_id='{}'".format(fund_id)
        cursor.execute(sql)
        scale = cursor.fetchall()[0]
        sizes.append(float(scale[2][:-1]))
    rates[0].sort()
    rates[1].sort()
    rates[2].sort()
    sizes.sort()
    for i in range(3):
        length = len(rates[i])
        bounds_rate[i].append(rates[i][length // 4])
        bounds_rate[i].append(rates[i][length // 2])
        bounds_rate[i].append(rates[i][3 * length // 4])
    length = len(sizes)
    bounds_size.append(sizes[length // 4])
    bounds_size.append(sizes[length // 2])
    bounds_size.append(sizes[3 * length // 4])
    print("rate bounds: ", end='')
    print(bounds_rate)
    print("scale bounds: ", end='')
    print(bounds_size)
    for fund in funds:
        fund_id = fund[0]
        vector_part = []
        # update rate
        sql = "select * from api_rate where fund_id='{}'".format(fund_id)
        cursor.execute(sql)
        try:
            rate = cursor.fetchall()[0]
            for i in range(3):
                if rate[i+1] == '---':
                    vector_part = vector_part + [0, 0, 0, 0]
                else:
                    value = float(rate[i+1][:-1])
                    if value < bounds_rate[i][0]:
                        vector_part = vector_part + [1, 0, 0, 0]
                    elif bounds_rate[i][0] <= value < bounds_rate[i][1]:
                        vector_part = vector_part + [0, 1, 0, 0]
                    elif bounds_rate[i][1] <= value < bounds_rate[i][2]:
                        vector_part = vector_part + [0, 0, 1, 0]
                    elif bounds_rate[i][2] < value:
                        vector_part = vector_part + [0, 0, 0, 1]
                    else:
                        vector_part = vector_part + [0, 0, 0, 0]
        except:
            vector_part = vector_part + [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]
        # update category
        category = fund[2]
        if category == 1:
            vector_part = vector_part + [1, 0, 0, 0, 0]
        elif category == 2:
            vector_part = vector_part + [0, 1, 0, 0, 0]
        elif category == 3:
            vector_part = vector_part + [0, 0, 1, 0, 0]
        elif category == 4:
            vector_part = vector_part + [0, 0, 0, 1, 0]
        elif category == 5:
            vector_part = vector_part + [0, 0, 0, 0, 1]
        else:
            vector_part = vector_part + [0, 0, 0, 0, 0]
        # update scale
        sql = "select * from api_size where fund_id='{}'".format(fund_id)
        cursor.execute(sql)
        scale = cursor.fetchall()[0]
        value = float(scale[2][:-1])
        if value < bounds_size[0]:
            vector_part = vector_part + [1, 0, 0, 0]
        elif bounds_size[0] <= value < bounds_size[1]:
            vector_part = vector_part + [0, 1, 0, 0]
        elif bounds_size[1] <= value < bounds_size[2]:
            vector_part = vector_part + [0, 0, 1, 0]
        elif bounds_size[2] < value:
            vector_part = vector_part + [0, 0, 0, 1]
        else:
            vector_part = vector_part + [0, 0, 0, 0]
        dic_fundV[fund_id][24:] = vector_part[:]
    print('update fund rate type scale ok')


def inferenceScore(dic_fundV, dic_preferenceV, dic_scoreV):
    for user_id in dic_scoreV.keys():
        dic_user_id = dic_scoreV[user_id]
        for fund_id in dic_user_id.keys():
            score = dic_user_id[fund_id]
            if score[0] == 0:
                new_score = float(np.dot(dic_preferenceV[user_id], dic_fundV[fund_id]))
                score[1] = new_score
    print("score inference ok")


def dic2database(db, cursor, dic, category):
    assert category in ['fund', 'preference', 'score']
    print("begin to write {} into database".format(category))
    if category == 'fund':
        for fund_id in dic.keys():
            sql = "update api_fundvector set day1={0[0]},day2={0[1]},day3={0[2]},day4={0[3]}," \
                  "week1={0[4]},week2={0[5]},week3={0[6]},week4={0[7]}," \
                  "month1={0[8]},month2={0[9]},month3={0[10]},month4={0[11]}," \
                  "three_month1={0[12]},three_month2={0[13]},three_month3={0[14]},three_month4={0[15]}," \
                  "six_month1={0[16]},six_month2={0[17]},six_month3={0[18]},six_month4={0[19]}," \
                  "year1={0[20]},year2={0[21]},year3={0[22]},year4={0[23]}," \
                  "stock1={0[24]},stock2={0[25]},stock3={0[26]},stock4={0[27]}," \
                  "bond1={0[28]},bond2={0[29]},bond3={0[30]},bond4={0[31]}," \
                  "cash1={0[32]},cash2={0[33]},cash3={0[34]},cash4={0[35]}," \
                  "type1={0[36]},type2={0[37]},type3={0[38]},type4={0[39]},type5={0[40]}," \
                  "scale1={0[41]},scale2={0[42]},scale3={0[43]},scale4={0[44]}" \
                  " where fund_id='{1}'".format(list(dic[fund_id]), fund_id)
            cursor.execute(sql)
            db.commit()
    elif category == 'preference':
        for user_id in dic.keys():
            sql = "update api_preferencevector set day1={0[0]},day2={0[1]},day3={0[2]},day4={0[3]}," \
                  "week1={0[4]},week2={0[5]},week3={0[6]},week4={0[7]}," \
                  "month1={0[8]},month2={0[9]},month3={0[10]},month4={0[11]}," \
                  "three_month1={0[12]},three_month2={0[13]},three_month3={0[14]},three_month4={0[15]}," \
                  "six_month1={0[16]},six_month2={0[17]},six_month3={0[18]},six_month4={0[19]}," \
                  "year1={0[20]},year2={0[21]},year3={0[22]},year4={0[23]}," \
                  "stock1={0[24]},stock2={0[25]},stock3={0[26]},stock4={0[27]}," \
                  "bond1={0[28]},bond2={0[29]},bond3={0[30]},bond4={0[31]}," \
                  "cash1={0[32]},cash2={0[33]},cash3={0[34]},cash4={0[35]}," \
                  "type1={0[36]},type2={0[37]},type3={0[38]},type4={0[39]},type5={0[40]}," \
                  "scale1={0[41]},scale2={0[42]},scale3={0[43]},scale4={0[44]}" \
                  " where user_id='{1}'".format(list(dic[user_id]), user_id)
            cursor.execute(sql)
            db.commit()
    elif category == 'score':
        for user_id in dic.keys():
            dic_user_id = dic[user_id]
            for fund_id in dic_user_id.keys():
                score = dic_user_id[fund_id]
                if score[0] == 0:
                    sql = "update api_userfundscore set score={}" \
                          " where user_id='{}' and fund_id='{}'".format(score[1], user_id, fund_id)
                    cursor.execute(sql)
        db.commit()
    print('write to database ok')

start_time = time.time()

db = pymysql.connect(host="114.115.160.16", user="root", password="123456Aa!", database="fund")
cursor = db.cursor()

dic_fundV, dic_preferenceV, dic_scoreV = database2dic(cursor)
updatePreferenceVector(dic_fundV, dic_preferenceV, dic_scoreV)
updateFundGrowth(cursor, dic_fundV)
# updateFundRateTypeScale(cursor, dic_fundV)
inferenceScore(dic_fundV, dic_preferenceV, dic_scoreV)
dic2database(db, cursor, dic_fundV, 'fund')
dic2database(db, cursor, dic_preferenceV, 'preference')

mid_time = time.time()
print('mid time: ', end='')
print(mid_time - start_time)

dic2database(db, cursor, dic_scoreV, 'score')


end_time = time.time()
print('time: ', end='') # 9160
print(end_time - start_time)
